package com.mcxx.modules.base.utils.excel;

import cn.afterturn.easypoi.excel.annotation.Excel;
import com.google.common.base.Strings;
import com.mcxx.common.exception.BizException;
import com.mcxx.modules.miniSalvation.fundpay.dto.CustomFundPayMemberDTO;
import com.mcxx.modules.miniSalvation.fundpay.entity.FundPayMemberEntity;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.BooleanUtils;
import org.apache.commons.lang3.CharUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;



@Slf4j
public class ImportExcel {


	private Workbook wb;


	private Sheet sheet;


	private int headerNum;



	public ImportExcel(MultipartFile multipartFile, int headerNum, int sheetIndex) throws IOException {
		this(multipartFile.getOriginalFilename(), multipartFile.getInputStream(), headerNum, sheetIndex);
	}


	public ImportExcel(String fileName, InputStream is, int headerNum, int sheetIndex) throws IOException {
		if (StringUtils.isBlank(fileName)){
			throw new BizException("-1", "导入文档为空!");
		}else if(fileName.toLowerCase().endsWith("xls")){
			this.wb = new HSSFWorkbook(is);
        }else if(fileName.toLowerCase().endsWith("xlsx")){
        	this.wb = new XSSFWorkbook(is);
        }else{
			throw new BizException("-1", "文档格式不正确!");
        }
		if (this.wb.getNumberOfSheets() < sheetIndex){
			throw new BizException("-1", "文档中没有工作表!");
		}
		this.sheet = this.wb.getSheetAt(sheetIndex);
		this.headerNum = headerNum;
	}


	public int getLastDataRowNum(){
		return this.sheet.getLastRowNum() + headerNum;
	}


	public Row getRow(int rownum){
		return this.sheet.getRow(rownum);
	}


	public Object getCellValue(Row row, int column){
		Object val = null;
		try{
			Cell cell = row.getCell(column);
			if(null == cell){
				return val;
			}
			if (cell.getCellType() == CellType.NUMERIC){
				val = cell.getNumericCellValue();
			}else if (cell.getCellType() == CellType.STRING){
				val = cell.getStringCellValue();
			}else if (cell.getCellType() == CellType.FORMULA){
				val = cell.getCellFormula();
			}else if (cell.getCellType() == CellType.BOOLEAN){
				val = cell.getBooleanCellValue();
			}else if (cell.getCellType() == CellType.ERROR){
				val = cell.getErrorCellValue();
			}
		}catch (Exception e) {
			return val;
		}
		return val;
	}
	private static String getCellValue(Cell cell) {
		if (cell == null) {
			return "";
		}
		if (cell.getCellType() == CellType.NUMERIC) {
			if (DateUtil.isCellDateFormatted(cell)) {
				return HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
			} else {
				return new BigDecimal(cell.getNumericCellValue()).toString();
			}
		} else if (cell.getCellType() == CellType.STRING) {
			return StringUtils.trimToEmpty(cell.getStringCellValue());
		} else if (cell.getCellType() == CellType.FORMULA) {
			return StringUtils.trimToEmpty(cell.getCellFormula());
		} else if (cell.getCellType() == CellType.BLANK) {
			return "";
		} else if (cell.getCellType() == CellType.BOOLEAN) {
			return String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == CellType.ERROR) {
			return "ERROR";
		} else {
			return cell.toString().trim();
		}

	}
	private static <T> void handleField(T t, String value, Field field) throws Exception {
		Class<?> type = field.getType();
		if (type == null || type == void.class || StringUtils.isBlank(value)) {
			return;
		}
		if (type == Object.class) {
			field.set(t, value);

		} else if (type.getSuperclass() == null || type.getSuperclass() == Number.class) {
			if (type == int.class || type == Integer.class) {
				field.set(t, NumberUtils.toInt(value));
			} else if (type == long.class || type == Long.class) {
				field.set(t, NumberUtils.toLong(value));
			} else if (type == byte.class || type == Byte.class) {
				field.set(t, NumberUtils.toByte(value));
			} else if (type == short.class || type == Short.class) {
				field.set(t, NumberUtils.toShort(value));
			} else if (type == double.class || type == Double.class) {
				field.set(t, NumberUtils.toDouble(value));
			} else if (type == float.class || type == Float.class) {
				field.set(t, NumberUtils.toFloat(value));
			} else if (type == char.class || type == Character.class) {
				field.set(t, CharUtils.toChar(value));
			} else if (type == boolean.class) {
				field.set(t, BooleanUtils.toBoolean(value));
			} else if (type == BigDecimal.class) {
				field.set(t, new BigDecimal(value));
			}
		} else if (type == Boolean.class) {
			field.set(t, BooleanUtils.toBoolean(value));
		} else if (type == Date.class) {

			field.set(t, value);
		} else if (type == String.class) {
			field.set(t, value);
		} else {
			Constructor<?> constructor = type.getConstructor(String.class);
			field.set(t, constructor.newInstance(value));
		}
	}



	public static <T> List<T> readExcel(Class<T> cls,MultipartFile file,InputStream is,boolean isFrist){
		String fileName = file.getOriginalFilename();
		if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
			log.error("上传文件格式不正确");
		}
		List<T> dataList = new ArrayList<>();
		Workbook workbook = null;
		try {
			if(null == is ){
				is = file.getInputStream();
			}
			if (fileName.toLowerCase().endsWith("xlsx")) {

				workbook = new XSSFWorkbook(is);
			}
			if (fileName.toLowerCase().endsWith("xls")) {

				workbook = new HSSFWorkbook(is);
			}
			if (workbook != null) {

				Map<String, List<Field>> classMap = new HashMap<>();
				List<Field> fields = Stream.of(cls.getDeclaredFields()).collect(Collectors.toList());
				fields.forEach(
						field -> {
							Excel annotation = field.getAnnotation(Excel.class);
							//ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
							if (annotation != null) {
								String value = annotation.name();
								if (StringUtils.isBlank(value)) {
									return;
								}
								if (!classMap.containsKey(value)) {
									classMap.put(value, new ArrayList<>());
								}
								field.setAccessible(true);
								classMap.get(value).add(field);
							}
						}
				);

				Map<Integer, List<Field>> reflectionMap = new HashMap<>(16);

				Sheet sheet = workbook.getSheetAt(0);

				boolean firstRow = true;
				int start = 0;
				if(!isFrist) start = sheet.getFirstRowNum()+1;
				for (int i = start; i <= sheet.getLastRowNum(); i++) {
					Row row = sheet.getRow(i);

					if (firstRow) {
						for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
							Cell cell = row.getCell(j);
							String cellValue = getCellValue(cell);
							if (classMap.containsKey(cellValue)) {
								reflectionMap.put(j, classMap.get(cellValue));
							}
						}
						firstRow = false;
					} else {

						if (row == null) {
							continue;
						}
						try {
							T t = cls.newInstance();

							boolean allBlank = true;
							for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
								if (reflectionMap.containsKey(j)) {
									Cell cell = row.getCell(j);
									String cellValue = getCellValue(cell);
									if (StringUtils.isNotBlank(cellValue)) {
										allBlank = false;
									}
									List<Field> fieldList = reflectionMap.get(j);
									fieldList.forEach(
											x -> {
												try {
													handleField(t, cellValue, x);
												} catch (Exception e) {
													log.error(String.format("reflect field:%s value:%s exception!", x.getName(), cellValue), e);
												}
											}
									);
								}
							}
							if (!allBlank) {
								dataList.add(t);
							} else {
								log.warn(String.format("row:%s is blank ignore!", i));
							}
						} catch (Exception e) {
							log.error(String.format("parse row:%s exception!", i), e);
						}
					}
				}
			}
		} catch (Exception e) {
			log.error(String.format("parse excel exception!"), e);
		} finally {
			if (workbook != null) {
				try {
					workbook.close();
				} catch (Exception e) {
					log.error(String.format("parse excel exception!"), e);
				}
			}
		}
		return dataList;
	}


	public static Map<String, CustomFundPayMemberDTO> CustomFundPayImportExcel(MultipartFile file){

		ImportExcel importExcel;
		try {
			importExcel = new ImportExcel(file, 1, 0);
		} catch (IOException e) {
			throw new BizException("-1", "excel文件解析失败");
		}


		Map<String, CustomFundPayMemberDTO> customFundPayMemberMap = new HashMap<>();
		for (int i = 2; i < importExcel.getLastDataRowNum(); i++) {
			Row row = importExcel.getRow(i);
			if ("".endsWith(importExcel.getCellValue(row, 3) + "") || null == importExcel.getCellValue(row, 3) + "") {
				continue;
			}

			String idcard = importExcel.getCellValue(row, 3).toString();
			if(StringUtils.isEmpty(idcard)){
				throw new BizException("-1", "第【" + i + "行】身份证号码为空，导入数据存在问题");
			}

			String number = importExcel.getCellValue(row, 7).toString();
			if(StringUtils.isEmpty(number)){
				throw new BizException("-1", "第【" + i + "行】在保月份次数为空，导入数据存在问题");
			}

			String fundNumber = importExcel.getCellValue(row, 8).toString();
			if(StringUtils.isEmpty(fundNumber)){
				throw new BizException("-1", "第【" + i + "行】补发金额为空，导入数据存在问题");
			}

			String memberName = importExcel.getCellValue(row, 2).toString();
			if(StringUtils.isEmpty(memberName)){
				throw new BizException("-1", "第【" + i + "行】成员姓名为空，导入数据存在问题");
			}

			Object remark = importExcel.getCellValue(row, 9);


			CustomFundPayMemberDTO entity = new CustomFundPayMemberDTO();
			entity.setFundNumber(Double.parseDouble(fundNumber) * Double.parseDouble(number));
			entity.setIdcard(idcard);
			entity.setName(memberName);
			if(null != remark){
				entity.setRemark(remark.toString());
			}
			if(customFundPayMemberMap.containsKey(idcard)){
				throw new BizException("-1", "第【" + i + "行】身份证存在多次，导入数据存在问题");
			}
			customFundPayMemberMap.put(idcard, entity);
		}
		if(customFundPayMemberMap.size() <= 0){
			throw new BizException("-1", "导入数据为空");
		}
		return customFundPayMemberMap;
	}


	public static Map<String, FundPayMemberEntity> importExcelCustomFundPay(MultipartFile file){

		ImportExcel importExcel;
		try {
			importExcel = new ImportExcel(file, 1, 0);
			importExcel.wb.close();
		} catch (IOException e) {
			throw new BizException("-1", "excel文件解析失败");
		}


		Map<String, FundPayMemberEntity> customFundPayMemberMap = new HashMap<>();

		int titleLast = importExcel.sheet.getRow(1).getLastCellNum();
		Row titleRow = importExcel.sheet.getRow(1);
		if (!"备注".equals(importExcel.getCellValue(titleRow, titleLast - 1)))
			throw new BizException("-1", "所用模板不正确，请使用标准模板");
		for (int i = 2; i < importExcel.getLastDataRowNum(); i++) {
			Row row = importExcel.getRow(i);
			if ("".endsWith(importExcel.getCellValue(row, 1) + "") || null == importExcel.getCellValue(row, 1)) {
				continue;
			}

			String idcard = importExcel.getCellValue(row, 1).toString().trim().substring(0,18);
			if (!StringUtils.isNotBlank(idcard)) {
				continue;
			}
			if(StringUtils.isEmpty(idcard)){
				throw new BizException("-1", "第【" + (i + 1) + "行】身份证号码为空，导入数据存在问题");
			}

			if (null == importExcel.getCellValue(row, 2)) {
				throw new BizException("-1", "第【" + (i + 1) + "行】补发金额为空，导入数据存在问题");
			}
			String fundNumber = importExcel.getCellValue(row, 2).toString();
			if(StringUtils.isEmpty(fundNumber)){
				throw new BizException("-1", "第【" + (i + 1) + "行】补发金额为空，导入数据存在问题");
			}

			if (null == importExcel.getCellValue(row, 0)) {
				throw new BizException("-1", "第【" + (i + 1) + "行】补发金额为空，导入数据存在问题");
			}
			String memberName = importExcel.getCellValue(row, 0).toString();
			if(StringUtils.isEmpty(memberName)){
				throw new BizException("-1", "第【" + (i + 1) + "行】成员姓名为空，导入数据存在问题");
			}

			Object remark = importExcel.getCellValue(row, 3);

			FundPayMemberEntity entity = new FundPayMemberEntity();
			entity.setFundNumber(Double.parseDouble(fundNumber) * 1);
			entity.setPersonNumber(1);
			entity.setIdcard(idcard);
			entity.setName(memberName);
			if(null != remark){
				entity.setRemark(remark.toString());
			}

			if(customFundPayMemberMap.containsKey(idcard)){
				throw new BizException("-1", "第【" + (i + 1) + "行】身份证存在多次，导入数据存在问题");
			}
			customFundPayMemberMap.put(idcard, entity);
		}
		if(customFundPayMemberMap.size() <= 0){
			throw new BizException("-1", "导入数据为空");
		}
		return customFundPayMemberMap;
	}


	public static List<FundPayMemberEntity> importExcelPersonInfo(MultipartFile file) {

		ImportExcel importExcel;
		try {
			importExcel = new ImportExcel(file, 1, 0);
			importExcel.wb.close();
		} catch (IOException e) {
			throw new BizException("-1", "excel文件解析失败");
		}
		List<FundPayMemberEntity> result = new ArrayList<>();
		Set<String> idCardSet = new HashSet<>();

		for (int i = 2; i < importExcel.getLastDataRowNum(); i++) {
			Row row = importExcel.getRow(i);
			if ("".endsWith(importExcel.getCellValue(row, 1) + "") || null == importExcel.getCellValue(row, 1)) {
				continue;
			}

			String idCard = importExcel.getCellValue(row, 1).toString().trim().substring(0,18);
			if (!StringUtils.isNotBlank(idCard)) {
				continue;
			}
			if(StringUtils.isEmpty(idCard)){
				throw new BizException("-1", "第【" + (i + 1) + "行】身份证号码为空，导入数据存在问题");
			}

			String memberName = importExcel.getCellValue(row, 0).toString();
			if(StringUtils.isEmpty(memberName)){
				throw new BizException("-1", "第【" + (i + 1) + "行】成员姓名为空，导入数据存在问题");
			}

			FundPayMemberEntity entity = new FundPayMemberEntity();
			entity.setIdcard(idCard);
			entity.setName(memberName);
			if(idCardSet.contains(idCard)){
				throw new BizException("-1", "第【" + (i + 1) + "行】身份证存在多次，导入数据存在问题");
			}
			idCardSet.add(idCard);
			result.add(entity);
		}
		if(result.size() <= 0){
			throw new BizException("-1", "导入数据为空");
		}
		return result;
	}
}
